
if exists (select 1 from sysobjects where name = 'get_dienstlisting')
   begin
     drop Procedure get_dienstlisting
     print 'Procedure: get_dienstlisting deleted ...'
   end
go

create procedure get_dienstlisting 
(
		@mandid     char(2) = ''
)
as
begin

	select --COUNT(dp.DienstID),
	       Anzeigename = d.Name + ' - ' + b.Kurztext,
	       DienstID    = dp.DienstID,
	       Name        = d.Name,
	       Kuzrtext    = b.Kurztext
	  from Dienstplan dp
	 inner join Dienst d
	    on dp.DienstID = d.DienstID
	 inner join Beschreibung b
	    on b.BeschrID = d.BeschrID
	 where d.mandid = @mandid
	 group by dp.DienstID, d.Name, b.Kurztext
	 order by count(dp.DienstID) desc


end
go

print 'Procedure: get_dienstlisting done ...'
go
grant exec on get_dienstlisting to prsadmins with grant option
go
grant exec on get_dienstlisting to prsusers
go
